package com.dshore.messagecenter.dao.warning;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.dshore.messagecenter.model.system.SysDict;
import com.dshore.messagecenter.model.warning.MrWarningPerson;
import com.dshore.messagecenter.model.warning.MrWarningThreshold;
import com.dshore.messagecenter.model.warning.WarningShow;

@Repository
public class WarningThresholdDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;
	/**
	 * 获取监控项下拉框数值
	 * @return
	 */
	public List<SysDict> getMonitoringItems(String objType) {
		StringBuffer sql = new StringBuffer();
		List<Object> list=new ArrayList<>();
		 sql.append("SELECT DICT_CODE,DICT_NAME FROM T_SYS_DICT WHERE DICT_TYPE='kpi_type' "
					+ "AND DICT_CODE IN (SELECT DISTINCT KPI_TYPE FROM T_MR_THRESHOLD_RELATION WHERE DEL_FLAG=0 ");
		 if(StringUtils.isNotBlank(objType)) {
			 sql.append(" and OBJ_TYPE=? ");
			 list.add(objType);
		 }
		 sql.append(")");
		 Object[] args=list.toArray();
		RowMapper<SysDict> rowMapper = new BeanPropertyRowMapper<>(SysDict.class);
		return jdbcTemplate.query(sql.toString(),args,rowMapper);
		
	}
	
	
	/**
	 * 获取监控时间段
	 * @return
	 */
	public List<SysDict> getMonitoringPeriod() {
		String sql="SELECT DICT_CODE,DICT_NAME FROM T_SYS_DICT WHERE DEL_FLAG=0 AND DICT_TYPE='period_time'";
		RowMapper<SysDict> rowMapper = new BeanPropertyRowMapper<>(SysDict.class);
		return jdbcTemplate.query(sql,rowMapper);
	}
	
	/**
	 * 预警阈值下拉框
	 * @return
	 */
	public List<SysDict> getThreshold(String objType,String kpiType) {
		StringBuffer sql =new StringBuffer();
		List<Object> list=new ArrayList<>();
		sql.append("SELECT DICT_CODE,DICT_NAME FROM T_SYS_DICT WHERE DEL_FLAG=0 AND DICT_TYPE='threshold_value_range'"
		+ "AND DICT_CODE IN (SELECT DISTINCT THRESHOLD_VALUE_RANGE FROM T_MR_THRESHOLD_RELATION WHERE DEL_FLAG=0");
		if(StringUtils.isNotBlank(objType)) {
			sql.append(" and OBJ_TYPE=? ");
			 list.add(objType);
		}
		if(StringUtils.isNotBlank(kpiType)) {
			sql.append(" and KPI_TYPE=? ");
			 list.add(kpiType);
		}
		sql.append(")");
		Object[] args=list.toArray();
		RowMapper<SysDict> rowMapper = new BeanPropertyRowMapper<>(SysDict.class);
		return jdbcTemplate.query(sql.toString(),args,rowMapper);
	}
	
	/**
	 * 预警通知人下拉值
	 * @return
	 */
	public List<MrWarningPerson> getPrincipal() {
		String sql="SELECT ID,PRINCIPAL FROM T_MR_WARNING_PERSON WHERE DEL_FLAG=0";
		RowMapper<MrWarningPerson> rowMapper = new BeanPropertyRowMapper<>(MrWarningPerson.class);
		return jdbcTemplate.query(sql,rowMapper);
	}
	
	/**
	 * 删除
	 * @param idArr
	 * @return
	 */
	public void deleteByIds(String[] idArr) {
		String sql="update T_MR_WARNING_THRESHOLD set del_flag=1,modified_time=?  where id =?";	
		 jdbcTemplate.batchUpdate(sql,new BatchPreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
				ps.setString(2, idArr[i].toString());
			}			
			@Override
			public int getBatchSize() {
				return idArr.length;
			}
		});
	}
	/**
	 * 通知方式下拉框值
	 * @return
	 */
	public List<SysDict> getWarningType() {
		String sql="SELECT DICT_CODE,DICT_NAME FROM T_SYS_DICT WHERE DEL_FLAG=0 AND DICT_TYPE='inform_way'";
		RowMapper<SysDict> rowMapper = new BeanPropertyRowMapper<>(SysDict.class);
		return jdbcTemplate.query(sql,rowMapper);
	}
	/**
	 * 总记录数
	 * @param warningThreshold
	 * @return
	 */
	public long queryCountByPrama(MrWarningThreshold warningThreshold) {
		String kpiId=warningThreshold.getKpiId();
		String objType=warningThreshold.getObjType();
		List<Object> list=new ArrayList<>();
		StringBuffer sql=new StringBuffer();
		 sql.append("select count(1) from T_MR_WARNING_THRESHOLD where Del_FLAG=0 ");
		 if(StringUtils.isNotBlank(kpiId)) {
			 sql.append("and KPI_ID =?");
			 list.add(kpiId);
		 }
		 if(StringUtils.isNotBlank(objType)) {
			 sql.append(" and OBJ_TYPE=?");
			 list.add(objType);
		 }
		 Object[] args=list.toArray();
		 try {
			 return jdbcTemplate.queryForObject(sql.toString(),args,long.class);
		 }catch(DataAccessException e) {
			 return 0;
		 }
	}
	/**
	 * 模糊查询
	 * @param warningThreshold
	 * @return
	 */
	public List<MrWarningThreshold> queryListByPrama(MrWarningThreshold warningThreshold) {
		String kpiId=warningThreshold.getKpiId();
		String objType=warningThreshold.getObjType();
		List<Object> list=new ArrayList<>();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from (");
		sql.append("select A.*, rownum rn from (select * from T_MR_WARNING_THRESHOLD  where del_flag = 0");		
		 if(StringUtils.isNotBlank(kpiId)) {
			 sql.append("and KPI_ID =?");
			 list.add(kpiId);
		 }
		 if(StringUtils.isNotBlank(objType)) {
			 sql.append(" and OBJ_TYPE=?");
			 list.add(objType);
		 }
		sql.append(" ) A   )");
		sql.append(" where rn between ? and ? ");
		sql.append(" order by created_time desc ");
		list.add(warningThreshold.getOffset()+1);
		list.add(warningThreshold.getOffset()+warningThreshold.getLimit());
		Object[] args=list.toArray();
		RowMapper<MrWarningThreshold> warningThresholdRowMapper = new BeanPropertyRowMapper<>(MrWarningThreshold.class);
		return jdbcTemplate.query(sql.toString(),args,warningThresholdRowMapper);
	}
	/**
	 * 获取监控类型下拉框值
	 * @return
	 */
	public List<SysDict> getMonitoringType() {
		String sql="SELECT DICT_CODE,DICT_NAME FROM T_SYS_DICT WHERE DICT_TYPE='obj_type' "
				+ "AND DICT_CODE IN (SELECT DISTINCT OBJ_TYPE FROM T_MR_THRESHOLD_RELATION WHERE DEL_FLAG=0) ";
		RowMapper<SysDict> rowMapper = new BeanPropertyRowMapper<>(SysDict.class);
		return jdbcTemplate.query(sql,rowMapper);
	}
	
	/**
	 * 获取表格数值名称
	 * @param warningThreshold1
	 * @return
	 */
	public WarningShow selectThresholdTable(MrWarningThreshold warningThreshold1){
		List<Object> list=new ArrayList<>();
		StringBuffer sql = new StringBuffer();
		  sql.append("select * from (SELECT dict_name as \"kpiName\" from T_SYS_DICT where dict_type='kpi_type' and dict_code=?  )");
		  list.add(warningThreshold1.getKpiId());
		  sql.append(",(select  dict_name as \"objtype\" from T_SYS_DICT where dict_type='obj_type' and dict_code=? )");
		  list.add(warningThreshold1.getObjType());
		/*  if("1701".equals(objType)) { //1701代表字典表 单个主题队列
			  sql.append(",( select  TOPIC_QUEUE_NAME as \"obj\"  from T_MD_TOPIC where id =?)");
			  list.add(warningThreshold1.getObj());
		  }*/
		  sql.append(",(select  dict_name as \"periodTime\" from T_SYS_DICT where dict_type='period_time' and dict_code=? )");
		  list.add(warningThreshold1.getMonitoringPeriod());
		  sql.append(",(select  dict_name as \"symbol\" from T_SYS_DICT where dict_type='threshold_value_range' and dict_code=? )");
		  list.add(warningThreshold1.getComparisonOperators());
		  sql.append(",(select dict_name as \"unitName\" from T_SYS_DICT where dict_type='unit' and dict_code=? )");
		  list.add(warningThreshold1.getUnitCode());
		  sql.append(",(select PRINCIPAL as \"people\" from T_MR_WARNING_PERSON where id=?)");
		  list.add(warningThreshold1.getPrincipalId());
		  sql.append(",(select  dict_name as \"informWay\" from T_SYS_DICT where dict_type='inform_way' and dict_code=? )");
		  list.add(warningThreshold1.getWarningType());
		  Object[] args=list.toArray();
		  RowMapper<WarningShow> RowMapper = new BeanPropertyRowMapper<>(WarningShow.class);		
			try {
				return jdbcTemplate.queryForObject(sql.toString(),args, RowMapper);
			}catch(DataAccessException e) {
				return null;
			}
	}
	
	/**
	 * 修改查询单个数据
	 * @param id
	 * @return
	 */
	public MrWarningThreshold getById(String id) {
		String sql="SELECT * FROM T_MR_WARNING_THRESHOLD WHERE ID=?";
		RowMapper<MrWarningThreshold> RowMapper = new BeanPropertyRowMapper<>(MrWarningThreshold.class);		
		return jdbcTemplate.queryForObject(sql, RowMapper,id);
	}
	
	/**
	 * 新增
	 * @param warningThreshold
	 * @return
	 */
	public Integer addWarningThreshold(MrWarningThreshold warningThreshold) {
		String sql="insert into T_MR_WARNING_THRESHOLD(id,KPI_ID,OBJ_TYPE,MONITORING_PERIOD,COMPARISON_OPERATORS"
				+ ",MONITORING_THRESHOLD,PRINCIPAL_ID,WARNING_TYPE,CREATED_TIME,UNIT_CODE) values (?,?,?,?,?,?,?,?,?,?)";
		return jdbcTemplate.update(sql,warningThreshold.getId(),warningThreshold.getKpiId(),warningThreshold.getObjType(),
				warningThreshold.getMonitoringPeriod(),warningThreshold.getComparisonOperators(),
				warningThreshold.getMonitoringThreshold(),warningThreshold.getPrincipalId(),warningThreshold.getWarningType(),new Date()
				,warningThreshold.getUnitCode());
	}
	
	/**
	 * 修改
	 * @param warningThreshold
	 * @return
	 */
	public Integer changeWarningThreshold(MrWarningThreshold warningThreshold) {
		String sql="update T_MR_WARNING_THRESHOLD set KPI_ID=?,OBJ_TYPE=?,MONITORING_PERIOD=?,"
				+ "COMPARISON_OPERATORS=?,MONITORING_THRESHOLD=?,PRINCIPAL_ID=?,WARNING_TYPE=?,MODIFIED_TIME=?,UNIT_CODE=? where ID=?";
		return jdbcTemplate.update(sql,warningThreshold.getKpiId(),warningThreshold.getObjType(),
				warningThreshold.getMonitoringPeriod(),warningThreshold.getComparisonOperators(),
				warningThreshold.getMonitoringThreshold(),warningThreshold.getPrincipalId(),warningThreshold.getWarningType(),new Date(),warningThreshold.getUnitCode(),warningThreshold.getId());
	}
	
	/**
	 * 获取单位
	 * @return
	 */
	public List<SysDict> getUnit(String objType,String kpiType) {
		StringBuffer sql = new StringBuffer();
		List<Object> list=new ArrayList<>();
		sql.append("SELECT DICT_CODE,DICT_NAME FROM T_SYS_DICT WHERE DEL_FLAG=0 AND DICT_TYPE='unit'"
				+ "AND DICT_CODE IN (SELECT DISTINCT UNIT FROM T_MR_THRESHOLD_RELATION WHERE DEL_FLAG=0");
		if(StringUtils.isNotBlank(objType)) {
			sql.append(" and OBJ_TYPE=? ");
			 list.add(objType);
		}
		if(StringUtils.isNotBlank(kpiType)) {
			sql.append(" and KPI_TYPE=? ");
			 list.add(kpiType);
		}
		sql.append(")");
		Object[] args=list.toArray();
		RowMapper<SysDict> rowMapper = new BeanPropertyRowMapper<>(SysDict.class);
		return jdbcTemplate.query(sql.toString(),args,rowMapper);
	}
	
	/**
	 * 删除主题队列同时删除预警阈值
	 * @param id
	 *//*
	@LogAnnotation(targetType="预警阈值",methodType="删除",dictType="kpi_type",fieldName="KPI_ID",queryArgs="OBJ",staticArgs="ids",tableName="T_MR_WARNING_THRESHOLD")
	public ResponseResult<Void> deleteFromTopic(String ids) {
		String[] idArr=ids.split(",");
		String sql="update T_MR_WARNING_THRESHOLD set del_flag=1,modified_time=?  where obj =?";	
		 jdbcTemplate.batchUpdate(sql,new BatchPreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
				ps.setString(2, idArr[i].toString());
			}			
			@Override
			public int getBatchSize() {
				return idArr.length;
			}
		});
		 return new ResponseResult<>(200);
	}*/
}
